package com.zlw.oracle;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import org.junit.Test;

import java.sql.*;

/**
 * @ClassName OracleDemo
 * @Description TODO
 * @Author zhaoluowei
 * @Date 2019/10/13  16:42
 * @Version 1.0
 *
 * 黑马双元57
 * https://www.bilibili.com/video/av68628979
 * https://www.bilibili.com/video/av68628979?p=33
 */

public class OracleDemo {

    @Test
    public void javaCallOracle() throws Exception {
        //加载 数据库 驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

        //得到预编译的对象
        String sql = "SELECT * FROM emp WHERE empno = ?";
        PreparedStatement state = connection.prepareStatement(sql);
        //设置参数
        state.setObject(1,7788);
        //执行数据库操作
        ResultSet resultSet = state.executeQuery();
        //输出结果
        while (resultSet.next()){
            System.out.println(resultSet.getString("ename"));
        }

        resultSet.close();
        state.close();
        connection.close();

    }

    /**
     * java调用存储过程
     * @throws Exception
     */
    @Test
    public void javaCallProcudure() throws Exception {
        //加载 数据库 驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

        //得到预编译的对象
        String sql = "{call p3(?,?)}";
        CallableStatement state = connection.prepareCall(sql);
        //设置参数
        state.setObject(1,7788);
        state.registerOutParameter(2, OracleTypes.NUMBER);
        //执行数据库操作
        boolean resultSet = state.execute();
        System.out.println("javaCallProcudure -- resultSet --> "+resultSet);
        if (resultSet){
            System.out.println("javaCallProcudure -- 执行失败 --> "+state.getInt(2));
        }else{
            System.out.println("javaCallProcudure -- 执行成功 --> "+state.getInt(2));
        }

        state.close();
        connection.close();

    }

    /**
     * java 调用存储方法
     * @throws Exception
     */
    @Test
    public void javaCallFunction() throws Exception {
        //加载 数据库 驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到connection连接
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

        //得到预编译的对象
        String sql = "{?=call f1(?)}";
        CallableStatement state = connection.prepareCall(sql);
        //设置参数
        state.setObject(2,7788);
        state.registerOutParameter(1, OracleTypes.NUMBER);
        //执行数据库操作
        boolean resultSet = state.execute();
        System.out.println("javaCallFunction -- resultSet --> "+resultSet);
        if (resultSet){
            System.out.println("javaCallFunction -- 执行失败 --> "+state.getInt(1));
        }else{
            System.out.println("javaCallFunction -- 执行成功 --> "+state.getInt(1));
        }

        state.close();
        connection.close();

    }

}
